<?php

namespace App\Http\Controllers\Personnel;

use App\Http\Controllers\Controller;
use App\Http\Controllers\HelperController;
use App\Http\Controllers\IDCardController;
use App\Imports\Personnel\ImportAdmin;
use App\Imports\Personnel\ImportDriver;
use App\Models\Personnel\Personnel;
use App\Models\Personnel\PersonnelAdmin;
use App\Models\Personnel\PersonnelDriver;
use App\Models\Personnel\PersonnelEduExperience;
use App\Models\Personnel\PersonnelFamily;
use App\Models\Personnel\PersonnelFile;
use App\Models\Personnel\PersonnelWorkExperience;
use App\User;
use Illuminate\Http\Request;
use Illuminate\Support\Str;
use Maatwebsite\Excel\Concerns\ToArray;
use Maatwebsite\Excel\Facades\Excel;
use App\Models\Personnel\PersonnelDeparture;
use App\Models\Personnel\PersonnelInjured;
use App\Models\ZipDownload;
use Mnabialek\LaravelSqlLogger\FileName;
use App\Models\Dict;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\IOFactory;

class PersonnelController extends Controller
{
    /**
     * 人员列表
     *
     * @param  Request  $request
     *
     * @return mixed
     */
    public function personnelList(Request $request)
    {
        $params = $countParams = $request->all();
        unset($countParams['pageNumber'], $countParams['pageSize']);
        $data = $this->getPersonnelList($params)->get()->toArray();
        if ($data) {
            $data = $this->transData($data);
        }
        $dataCount = $this->getPersonnelList($countParams)->count();
        if (isset($params['type'])) {
            if ($params['type'] === 1) {
                $data = collect($data)->sortBy('line_num')->values()->toArray();
            } else {
                $data = sortArrByManyField($data, 'department_sort', SORT_ASC, 'sort', SORT_ASC);
            }
        }
        return response()->formatJson(['personnel' => $data, 'count' => $dataCount]);
    }

    public function getPersonnelList($params): \Illuminate\Database\Eloquent\Builder
    {
        // 人员类型
        $query = Personnel::query()->select('id', 'name', 'IDCard', 'IDCard_url', 'residential_address',
            'IDCard_address', 'phone', 'join_at', 'status', 'created_at', 'sort', 'note', 'positive_at',
            'contract_expire_at', 'departure_at', 'departure_at', 'endowment_insurance_payment_at',
            'unemployment_insurance_payment_at', 'maternity_insurance_payment_at', 'medical_insurance_payment_at',
            'injured_insurance_payment_at', 'education', 'graduated_school');


        if (isset($params['name'])) {
            $query->where('name', 'like', '%'.$params['name'].'%');
        }

        // 人员状态
        if (isset($params['status'])) {
            $query->where('status', $params['status']);
            if ($params['status'] == 1) {
                $query->with('departure');
            }
        }

        // 人员类型
        if (isset($params['type'])) {
            if ($params['type'] === 1) {
                $query->with('driverInfo')->where('type', $params['type']);
            } else {
                $query->with('adminInfo')->where('type', $params['type']);
            }
        }

        if (isset($params['is_injured'])) {
            $query->whereIn('is_injured', [$params['is_injured']]);
        }
        // if (isset($params['pageNumber'], $params['pageSize'])) {
        //     $query
        //         ->limit($params['pageSize'])
        //         ->offset(($params['pageNumber'] - 1) * $params['pageSize']);
        // }

        return $query;
    }

    /**
     * 数据转化
     *
     * @param  array  $data
     *
     * @return array
     */
    public function transData(array $data): array
    {
        $department = Dict::with('data')->where('title', '通讯录部门')->get()->toArray();
        foreach ($data as $key => $personnel) {
            foreach ($personnel as $filed => $value) {
                if ($filed === 'admin_info') {
                    if ($value) {
                        foreach ($department[0]['data'] as $dv) {
                            if ($dv['title'] == $value['department_id']) {
                                $data[$key]['department_sort'] = $dv['sort'];
                            } else {
                                $data[$key]['department_sort'] = 0;
                            }
                        }
                        $data[$key]['department'] = $value['department_id'];
                        $data[$key]['office'] = $value['office_id'];
                    }

                    unset($data[$key][$filed]);
                }
                if ($filed === 'driver_info') {
                    if ($value) {
                        $data[$key]['team_num'] = $value['team_num'];
                        $data[$key]['line_num'] = $value['line_num'];
                        $data[$key]['driving_license_expire_at'] = $value['driving_license_expire_at'];
                        $data[$key]['work_license_expire_at'] = $value['work_license_expire_at'];
                        $data[$key]['is_join_tu'] = $value['is_join_tu'];
                    }

                    unset($data[$key][$filed]);
                }

                if ($filed === 'IDCard') {
                    $data[$key]['age'] = IDCardController::getAge($value);
                    $data[$key]['sex'] = IDCardController::getSex($value);
                    $data[$key]['birthday'] = IDCardController::getBirthday($value);
                }

                if ($filed === 'join_at') {
                    $data[$key]['join_at'] = $value === '0000-00-00' ? '' : $value;
                }
            }
        }

        return $data;
    }

    /**
     * 创建标签
     *
     * @param  Request  $request
     *
     * @return mixed
     */
    public function store(Request $request)
    {
        $params = $request->all();

        $params['status'] = isset($params['status']) ? $params['status'] : 0;
        $params['IDCard_expire_at'] = $params['IDCard_expire_at'] ? date('Y-m-d',
            strtotime($params['IDCard_expire_at'])) : null;
        $params['graduated_at'] = $params['graduated_at'] ? date('Y-m-d', strtotime($params['graduated_at'])) : null;
        $params['work_at'] = $params['work_at'] ? date('Y-m-d', strtotime($params['work_at'])) : null;
        $params['join_at'] = $params['join_at'] ? date('Y-m-d', strtotime($params['join_at'])) : null;
        $params['positive_at'] = $params['positive_at'] ? date('Y-m-d', strtotime($params['positive_at'])) : null;
        $params['contract_expire_at'] = $params['contract_expire_at'] ? date('Y-m-d',
            strtotime($params['contract_expire_at'])) : null;
        $params['endowment_insurance_payment_at'] = $params['endowment_insurance_payment_at'] ? date('Y-m-d',
            strtotime($params['endowment_insurance_payment_at'])) : null;
        $params['medical_insurance_payment_at'] = $params['medical_insurance_payment_at'] ? date('Y-m-d',
            strtotime($params['medical_insurance_payment_at'])) : null;
        $params['unemployment_insurance_payment_at'] = $params['unemployment_insurance_payment_at'] ? date('Y-m-d',
            strtotime($params['unemployment_insurance_payment_at'])) : null;
        $params['injured_insurance_payment_at'] = $params['injured_insurance_payment_at'] ? date('Y-m-d',
            strtotime($params['injured_insurance_payment_at'])) : null;
        $params['maternity_insurance_payment_at'] = $params['maternity_insurance_payment_at'] ? date('Y-m-d',
            strtotime($params['maternity_insurance_payment_at'])) : null;

        $personnel = new Personnel($params);
        $result = $personnel->save();
        $message = $result ? '添加成功' : '添加失败';

        if ($params['type'] === '0') {
            $adminInfo = [
                'department_id' => $params['department_id'],
                'office_id'     => $params['office_id'],
                'is_join_tu'    => $params['is_join_tu'],
                'work_phone'    => $params['work_phone'],
                'short_number'  => $params['short_number'],
            ];
            $admin = new PersonnelAdmin($adminInfo);
            $personnel->adminInfo()->save($admin);

            // 关联资质文件
            if (! empty($params['adminFile'])) {
                foreach ($params['adminFile'] as $work) {
                    $work['type'] = 'admin';
                    $file = new PersonnelFile($work);
                    $personnel->file()->save($file);
                }
            }
            // 关联司机资质文件
            if (! empty($params['driverFile'])) {
                foreach ($params['driverFile'] as $work) {
                    $work['type'] = 'driver';
                    $file = new PersonnelFile($work);
                    $personnel->file()->save($file);
                }
            }
        } else {
            $driverInfo = [
                'team_num'          => $params['team_num'],
                'line_num'          => $params['line_num'],
                'is_join_tu'        => $params['is_join_tu'],
                'is_injured'        => isset($params['is_injured']) ? $params['is_injured'] : '',
                'bank_card_number'  => $params['bank_card_number'],
                'bank'              => $params['bank'],
                'injured_status'    => isset($params['injured_status']) ? $params['injured_status'] : '',
                'date'              => isset($params['date']) ? $params['date'] : '',
                'place'             => isset($params['place']) ? $params['place'] : '',
                'reason'            => isset($params['reason']) ? $params['reason'] : '',
                'description'       => isset($params['description']) ? $params['description'] : '',
                'injured_photo_url' => isset($params['injured_photo_url']) ? $params['injured_photo_url'] : '',
            ];
            $driver = new PersonnelDriver($driverInfo);
            $personnel->driverInfo()->save($driver);
        }

        // 关联工作经历
        if (! empty($params['works'])) {
            foreach ($params['works'] as $work) {
                if ($work['status'] === 1) {
                    $workExperience = new PersonnelWorkExperience($work);
                    $personnel->workExperience()->save($workExperience);
                }
            }
        }

        // 关联教育经历
        if (! empty($params['edus'])) {
            foreach ($params['edus'] as $edu) {
                if ($edu['status'] === 1) {
                    $eduExperience = new PersonnelEduExperience($edu);
                    $personnel->eduExperience()->save($eduExperience);
                }
            }
        }

        // 关联家庭成员
        if (! empty($params['family'])) {
            foreach ($params['family'] as $person) {
                if ($person['status'] === 1) {
                    $family = new PersonnelFamily($person);
                    $personnel->family()->save($family);
                }
            }
        }
        if (isset($params['status']) && $params['status'] == 1) {
            $departure_data = ['departure_at' => $params['departure_at']];
            $departure = new PersonnelDeparture($departure_data);
            $personnel->departure()->save($departure);
        }

        return response()->formatJson($result, $message);
    }


    /**
     * 获取车辆数据到编辑表单页面
     *
     * @param $id
     *
     * @return mixed
     */
    public function edit($id)
    {
        $personnel = Personnel::with('adminInfo')
            ->with('driverInfo')
            ->with('eduExperience')
            ->with('workExperience')
            ->with('family')
            ->with('file')
            ->find($id)
            ->toArray();
        $personnel['age'] = IDCardController::getAge($personnel['IDCard']);
        $personnel['sex'] = IDCardController::getSex($personnel['IDCard']);
        $personnel['IDCard_expire_at'] = $personnel['IDCard_expire_at'] != '0000:00:00' && $personnel['IDCard_expire_at'] != null ? date('Y-m-d',
            strtotime($personnel['IDCard_expire_at'])) : null;
        $personnel['graduated_at'] = $personnel['graduated_at'] != '0000:00:00' && $personnel['graduated_at'] != null ? date('Y-m-d',
            strtotime($personnel['graduated_at'])) : null;
        $personnel['work_at'] = $personnel['work_at'] != '0000:00:00' && $personnel['work_at'] != null ? date('Y-m-d',
            strtotime($personnel['work_at'])) : null;
        $personnel['join_at'] = $personnel['join_at'] != '0000:00:00' && $personnel['join_at'] != null ? date('Y-m-d',
            strtotime($personnel['join_at'])) : null;
        $personnel['positive_at'] = $personnel['positive_at'] != '0000:00:00' && $personnel['positive_at'] != null ? date('Y-m-d',
            strtotime($personnel['positive_at'])) : null;
        $personnel['contract_expire_at'] = $personnel['contract_expire_at'] != '0000:00:00' && $personnel['contract_expire_at'] != null ? date('Y-m-d',
            strtotime($personnel['contract_expire_at'])) : null;
        $personnel['endowment_insurance_payment_at'] = $personnel['endowment_insurance_payment_at'] != '0000:00:00' && $personnel['endowment_insurance_payment_at'] != null ? date('Y-m-d',
            strtotime($personnel['endowment_insurance_payment_at'])) : null;
        $personnel['medical_insurance_payment_at'] = $personnel['medical_insurance_payment_at'] != '0000:00:00' && $personnel['medical_insurance_payment_at'] != null ? date('Y-m-d',
            strtotime($personnel['medical_insurance_payment_at'])) : null;
        $personnel['unemployment_insurance_payment_at'] = $personnel['unemployment_insurance_payment_at'] != '0000:00:00' && $personnel['unemployment_insurance_payment_at'] != null ? date('Y-m-d',
            strtotime($personnel['unemployment_insurance_payment_at'])) : null;
        $personnel['injured_insurance_payment_at'] = $personnel['injured_insurance_payment_at'] != '0000:00:00' && $personnel['injured_insurance_payment_at'] != null ? date('Y-m-d',
            strtotime($personnel['injured_insurance_payment_at'])) : null;
        $personnel['maternity_insurance_payment_at'] = $personnel['maternity_insurance_payment_at'] != '0000:00:00' && $personnel['maternity_insurance_payment_at'] != null ? date('Y-m-d',
            strtotime($personnel['maternity_insurance_payment_at'])) : null;

        if ($personnel['admin_info']) {
            $personnel['department_id'] = $personnel['admin_info']['department_id'];
            $personnel['office_id'] = $personnel['admin_info']['office_id'];
            $personnel['work_phone'] = $personnel['admin_info']['work_phone'];
            $personnel['is_join_tu'] = $personnel['admin_info']['is_join_tu'];
            $personnel['short_number'] = $personnel['admin_info']['short_number'];
        }
        if ($personnel['driver_info']) {
            $personnel['team_num'] = $personnel['driver_info']['team_num'];
            $personnel['line_num'] = $personnel['driver_info']['line_num'];
            $personnel['is_join_tu'] = $personnel['driver_info']['is_join_tu'];
            $personnel['bank_card_number'] = $personnel['driver_info']['bank_card_number'];
            $personnel['bank'] = $personnel['driver_info']['bank'];
            $personnel['injured_status'] = isset($personnel['driver_info']['injured_status']) ? $personnel['driver_info']['injured_status'] : '';
            $personnel['date'] = $personnel['driver_info']['date'];
            $personnel['place'] = $personnel['driver_info']['place'];
            $personnel['reason'] = $personnel['driver_info']['reason'];
            $personnel['description'] = $personnel['driver_info']['description'];
            $personnel['injured_photo_url'] = $personnel['driver_info']['injured_photo_url'];
        }
        $edus = [];
        if ($personnel['edu_experience']) {
            foreach ($personnel['edu_experience'] as $k => $v) {
                $v['index'] = $k + 1;
                $v['status'] = 1;
                $edus[] = $v;
            }
        }
        $personnel['edus'] = $edus;

        $works = [];
        if ($personnel['work_experience']) {
            foreach ($personnel['work_experience'] as $k => $v) {
                $v['index'] = $k + 1;
                $v['status'] = 1;
                $works[] = $v;
            }
        }
        $personnel['works'] = $works;

        $family = [];
        if ($personnel['family']) {
            foreach ($personnel['family'] as $k => $v) {
                $v['index'] = $k + 1;
                $v['status'] = 1;
                $family[] = $v;
            }
        }
        $personnel['family'] = $family;

        unset(
            $personnel['edu_experience'],
            $personnel['work_experience'],
            $personnel['admin_info'],
            $personnel['driver_info']
        );

        $users = User::all();
        if (optional($personnel['file'])) {
            foreach ($personnel['file'] as $k => $file) {
                $personnel['file'][$k]['user_name'] = $users->where('id', $file['user_id'])->first()->name;
            }
        }

        return response()->formatJson($personnel);
    }

    public function slogan()
    {
        return view('slogan');
    }

    /**
     * 修改文章
     *
     * @param  Request  $request
     * @param           $id
     *
     * @return mixed
     */
    public function update(Request $request, $id)
    {
        $params = $request->all();

        $personnel = Personnel::query()->find($id);
        if (isset($params['status']) && $params['status'] == 1) {
            PersonnelDeparture::insert(['departure_at' => $params['departure_at'], 'personnel_id' => $id]);
        }
        $params['IDCard_expire_at'] = $params['IDCard_expire_at'] ? date('Y-m-d',
            strtotime($params['IDCard_expire_at'])) : null;
        $params['graduated_at'] = $params['graduated_at'] ? date('Y-m-d', strtotime($params['graduated_at'])) : null;
        $params['work_at'] = $params['work_at'] ? date('Y-m-d', strtotime($params['work_at'])) : null;
        $params['join_at'] = $params['join_at'] ? date('Y-m-d', strtotime($params['join_at'])) : null;
        $params['positive_at'] = $params['positive_at'] ? date('Y-m-d', strtotime($params['positive_at'])) : null;
        $params['contract_expire_at'] = $params['contract_expire_at'] ? date('Y-m-d',
            strtotime($params['contract_expire_at'])) : null;
        $params['endowment_insurance_payment_at'] = $params['endowment_insurance_payment_at'] ? date('Y-m-d',
            strtotime($params['endowment_insurance_payment_at'])) : null;
        $params['medical_insurance_payment_at'] = $params['medical_insurance_payment_at'] ? date('Y-m-d',
            strtotime($params['medical_insurance_payment_at'])) : null;
        $params['unemployment_insurance_payment_at'] = $params['unemployment_insurance_payment_at'] ? date('Y-m-d',
            strtotime($params['unemployment_insurance_payment_at'])) : null;
        $params['injured_insurance_payment_at'] = $params['injured_insurance_payment_at'] ? date('Y-m-d',
            strtotime($params['injured_insurance_payment_at'])) : null;
        $params['maternity_insurance_payment_at'] = $params['maternity_insurance_payment_at'] ? date('Y-m-d',
            strtotime($params['maternity_insurance_payment_at'])) : null;

        $result = $personnel->update($params);
        $message = $result ? '修改成功' : '修改失败';

        if ($params['type'] === 0) {
            $adminInfo = [
                'department_id' => $params['department_id'],
                'office_id'     => $params['office_id'],
                'is_join_tu'    => $params['is_join_tu'],
                'work_phone'    => $params['work_phone'],
                'short_number'  => $params['short_number'],
            ];
            $personnel->adminInfo()->updateOrCreate(
                ['personnel_id' => $id],
                $adminInfo
            );
        } else {
            $driverInfo = [
                'team_num'          => $params['team_num'],
                'line_num'          => $params['line_num'],
                'is_join_tu'        => $params['is_join_tu'],
                'bank_card_number'  => $params['bank_card_number'],
                'bank'              => $params['bank'],
                'injured_status'    => $params['injured_status'],
                'date'              => $params['date'],
                'place'             => $params['place'],
                'reason'            => $params['reason'],
                'description'       => $params['description'],
                'injured_photo_url' => $params['injured_photo_url'],
            ];
            $personnel->driverInfo()->updateOrCreate(
                ['personnel_id' => $id],
                $driverInfo
            );
        }

        // 关联工作经历
        if (! empty($params['works'])) {
            foreach ($params['works'] as $work) {
                if (! array_key_exists('id', $work)) {
                    if ($work['status'] === 1) {
                        $workExperience = new PersonnelWorkExperience($work);
                        $personnel->workExperience()->save($workExperience);
                    }
                } else {
                    $workExperience = PersonnelWorkExperience::query()->find($work['id']);
                    $workExperience->update($work);
                }
            }
        }

        // 关联教育经历
        if (! empty($params['edus'])) {
            foreach ($params['edus'] as $edu) {
                if (! array_key_exists('id', $edu)) {
                    if ($edu['status'] === 1) {
                        $eduExperience = new PersonnelEduExperience($edu);
                        $personnel->eduExperience()->save($eduExperience);
                    }
                } else {
                    $workExperience = PersonnelEduExperience::query()->find($edu['id']);
                    $workExperience->update($edu);
                }
            }
        }

        // 关联家庭成员
        if (! empty($params['family'])) {
            foreach ($params['family'] as $person) {
                if (! array_key_exists('id', $person)) {
                    if ($person['status'] === 1) {
                        $family = new PersonnelFamily($person);
                        $personnel->family()->save($family);
                    }
                } else {
                    $workExperience = PersonnelFamily::query()->find($person['id']);
                    $workExperience->update($person);
                }
            }
        }

        // 关联资质文件
        if (! empty($params['adminFile'])) {
            foreach ($params['adminFile'] as $work) {
                if (! array_key_exists('type', $work)) {
                    $work['type'] = 'admin';
                    $file = new PersonnelFile($work);
                    $personnel->file()->save($file);
                }
            }
        }
        // 关联司机资质文件
        if (! empty($params['driverFile'])) {
            foreach ($params['driverFile'] as $work) {
                if (! array_key_exists('type', $work)) {
                    $work['type'] = 'driver';
                    $file = new PersonnelFile($work);
                    $personnel->file()->save($file);
                }
            }
        }

        return response()->formatJson($result, $message);
    }

    /**
     * 删除人员
     *
     * @param  Request  $request
     *
     * @return mixed
     */
    public function destroy(Request $request)
    {
        $result = false;
        if ($request->has('ids')) {
            $ids = explode(',', $request['ids']);
            $delCount = Personnel::destroy($ids);
            $result = $delCount !== 0;
        }

        $message = $result ? '删除成功' : '删除失败';

        return response()->formatJson($result, $message);
    }

    /**
     *  导入行政人员数据
     *
     * @param  Request  $request
     *
     * @return mixed
     * @throws \Exception
     */
    public function importAdmin(Request $request)
    {
        $urlParse = parse_url($request->input('url'));
        $excelData = Excel::toArray(new ImportAdmin, public_path().$urlParse['path']);
        $fullData = $excelData[0];

        array_shift($fullData);
        array_shift($fullData);

        $fields = [
            'department_id',
            'sort',
            'name',
            'office_id',
            'join_at',
            'positive_at',
            'contract_expire_at',
            'IDCard',
            'age',
            'sex',
            'IDCard_address',
            'phone',
            'pass1',
            'pass2',
            'pass3',
            'education',
            'graduated_school',
            'pass4',
            'pass5',
            'note',
        ];
        // 整理数据，填充合并的单元格数据
        foreach ($fullData as $row) {
            $item = [];
            foreach ($fields as $index => $field) {
                $item[$field] = $row[$index];
            }
            $item['type'] = 0;
            $item['name'] = Str::of($item['name'])->trim()->replace(' ', '');
            if (is_int($item['join_at'])) {
                $item['join_at'] = HelperController::transformDateTime($item['join_at']);
            }
            if (is_int($item['positive_at'])) {
                $item['positive_at'] = HelperController::transformDateTime($item['positive_at']);
            }
            if (is_int($item['contract_expire_at'])) {
                $item['contract_expire_at'] = HelperController::transformDateTime($item['contract_expire_at']);
            }
            $p_id = Personnel::where('IDCard', $item['IDCard'])->where('phone', $item['phone'])
                ->where('name', $item['name'])->value('id');
            if ($p_id > 0) {
                $adminInfo = [
                    'department_id' => Str::of($item['department_id'])->trim()->replace(' ', ''),
                    'office_id'     => Str::of($item['office_id'])->trim()->replace(' ', ''),
                ];
                unset($item['department_id'], $item['office_id'], $item['age'], $item['sex'], $item['pass1'], $item['pass2'], $item['pass3'], $item['pass4'], $item['pass5']);

                $personnel = Personnel::query()->find($p_id);
                $personnel->update($item);
                $personnel->adminInfo()->updateOrCreate(
                    ['personnel_id' => $p_id],
                    $adminInfo
                );
            } else {
                $personnel = new Personnel($item);
                $personnel->save();
                $adminInfo = [
                    'department_id' => Str::of($item['department_id'])->trim()->replace(' ', ''),
                    'office_id'     => Str::of($item['office_id'])->trim()->replace(' ', ''),
                ];
                $admin = new PersonnelAdmin($adminInfo);
                $personnel->adminInfo()->save($admin);
            }
        }

        return response()->formatJson(true, '导入成功');
    }

    /**
     * 导入驾驶员数据
     *
     * @param  Request  $request
     *
     * @return mixed
     * @throws \Exception
     */
    public function importDriver(Request $request)
    {
        $urlParse = parse_url($request->input('url'));
        $excelData = Excel::toArray(new importDriver, public_path().$urlParse['path']);
        $fullData = $excelData[0];

        array_shift($fullData);

        $fields = [
            'sort',
            'team_num',
            'line_num',
            'name',
            'join_at',
            'contract_expire_at',
            'safe',
            'length_of_service',
            'phone',
            'IDCard',
            'age',
            'sex',
            'residential_address',
            'yanglao',
            'shiye',
            'yiliao',
            'gongshang',
            'driving_license_expire_at',
            'work_license_expire_at',
            'pass1',
            'pass2',
            'pass3',
            'is_join_tu',
            'note',
        ];
        $unsetKeys = [];
        // 整理数据，填充合并的单元格数据
        foreach ($fullData as $k => $row) {
            if (! array_filter($row)) {
                $unsetKeys[] = $k;
            }
        }
        // 删除空数据
        if ($unsetKeys) {
            foreach ($unsetKeys as $v) {
                unset($fullData[$v]);
            }
        }
        foreach ($fullData as $k => $row) {
            $item = [];
            foreach ($fields as $index => $field) {
                $item[$field] = $row[$index];
            }
            $item['type'] = 1;
            if ($item['sex']) {
                $IDCard = new IDCardController();
                $item['sex'] = $IDCard::getSex($item['IDCard']);
            }
            if ($item['join_at'] && is_int($item['join_at'])) {
                $item['join_at'] = HelperController::transformDateTime($item['join_at']);
            }
            if ($item['contract_expire_at'] && is_int($item['contract_expire_at'])) {
                $item['contract_expire_at'] = HelperController::transformDateTime($item['contract_expire_at']);
            }

            if ($item['driving_license_expire_at'] && is_int($item['driving_license_expire_at'])) {
                $item['driving_license_expire_at'] = HelperController::transformDateTime($item['driving_license_expire_at']);
            }
            if ($item['work_license_expire_at'] && is_int($item['work_license_expire_at'])) {
                $item['work_license_expire_at'] = HelperController::transformDateTime($item['work_license_expire_at']);
            }
            $item['name'] = trim(str_replace(' ', '', $item['name']));
            $p_id = Personnel::where('IDCard', $item['IDCard'])->where('phone', $item['phone'])
                ->where('name', $item['name'])->value('id');
            if ($p_id > 0) {
                $driverInfo = [
                    'team_num'                  => $item['team_num'],
                    'line_num'                  => $item['line_num'],
                    'is_join_tu'                => $item['is_join_tu'] === '是' ? 1 : 0,
                    'driving_license_expire_at' => $item['driving_license_expire_at'],
                    'work_license_expire_at'    => $item['work_license_expire_at'],
                ];
                unset($item['department_id'], $item['office_id'], $item['age'], $item['sex'], $item['pass1'], $item['pass2'], $item['pass3']);

                $personnel = Personnel::query()->find($p_id);
                $personnel->update($item);
                $personnel->driverInfo()->updateOrCreate(
                    ['personnel_id' => $p_id],
                    $driverInfo
                );
            } else {
                $personnel = new Personnel($item);
                $personnel->save();
                $driverInfo = [
                    'team_num'                  => $item['team_num'],
                    'line_num'                  => $item['line_num'],
                    'is_join_tu'                => $item['is_join_tu'] === '是' ? 1 : 0,
                    'driving_license_expire_at' => $item['driving_license_expire_at'],
                    'work_license_expire_at'    => $item['work_license_expire_at'],
                ];
                $driver = new PersonnelDriver($driverInfo);
                $personnel->driverInfo()->save($driver);
            }
        }

        return response()->formatJson(true, '导入成功');
    }

    public function getAllPersonnel()
    {
        $personnel = Personnel::all()->groupBy('type')->toArray();
        $tree = [];
        if ($personnel) {
            foreach ($personnel as $type => $person) {
                $child = [
                    'title'  => $type === 1 ? '驾驶员' : '行政',
                    'expand' => false,
                ];
                foreach ($person as $k => $v) {
                    $v['title'] = $v['name'];
                    $v['expand'] = true;
                    $child['children'][] = $v;
                }
                $tree[] = $child;
            }
        }

        return response()->formatJson(['result' => $tree]);
    }

    /**
     * 离职人员列表
     *
     * @param  Request  $request
     *
     * @return mixed
     */
    public function departureList(Request $request)
    {
        $params = $countParams = $request->all();
        unset($countParams['pageNumber'], $countParams['pageSize']);
        $data = $this->getDepartureList($params)->get()->toArray();
        if ($data) {
            $data = $this->departureTransData($data);
        }
        $dataCount = $this->getDepartureList($countParams)->count();
        return response()->formatJson(['personnel' => $data, 'count' => $dataCount]);
    }

    public function getDepartureList($params): \Illuminate\Database\Eloquent\Builder
    {
        $query = PersonnelDeparture::query();

        if (isset($params['personnel_id'])) {
            $query->where('personnel_id', $params['personnel_id']);
        }

        $query->with('personnel');

        // if (isset($params['pageNumber'], $params['pageSize'])) {
        //     $query
        //         ->limit($params['pageSize'])
        //         ->offset(($params['pageNumber'] - 1) * $params['pageSize']);
        // }

        return $query;
    }

    /**
     * 数据转化
     *
     * @param  array  $data
     *
     * @return array
     */
    public function departureTransData(array $data): array
    {
        foreach ($data as $key => $personnel) {
            foreach ($personnel as $filed => $value) {

                if ($filed === 'personnel') {
                    $data[$key]['name'] = $value['name'];
                    $data[$key]['join_at'] = $value['join_at'];
                    $data[$key]['IDCard'] = $value['IDCard'];
                    $data[$key]['phone'] = $value['phone'];
                    $data[$key]['age'] = IDCardController::getAge($value['IDCard']);
                    $data[$key]['sex'] = IDCardController::getSex($value['IDCard']);
                }

            }
        }

        return $data;
    }

    /**
     * 添加离职人员
     */
    public function addDeparturePersonnel(Request $request)
    {
        $params = $request->all();
        unset($params['name'], $params['IDCard'], $params['phone'], $params['join_at'], $params['age'], $params['sex']);
        $personnel = Personnel::query()->where('id', $params['personnel_id'])->update([
            'status' => 1, 'departure_at' => $params['departure_at'],
        ]);
        $params['created_at'] = date('Y-m-d H:i:s');
        $result = PersonnelDeparture::insert($params);
        $message = $result ? '添加成功' : '添加失败';
        return response()->formatJson($result, $message);
    }

    /**
     * 添加工伤人员
     */
    public function editDeparturePersonnel(Request $request)
    {
        $params = $request->all();
        unset($params['personnel_name'], $params['IDCard'], $params['IDCard'], $params['phone'], $params['join_at'], $params['age'], $params['sex']);
        $personnel = Personnel::query()->where('id', $params['personnel_id'])->update([
            'status' => 1, 'departure_at' => $params['departure_at'],
        ]);
        $result = PersonnelDeparture::where('id', $params['id'])
            ->update([
                'departure_reason' => $params['departure_reason'], 'departure_at' => $params['departure_at'],
                'updated_at'       => date('Y-m-d H:i:s'),
            ]);
        $message = $result ? '添加成功' : '添加失败';
        return response()->formatJson($result, $message);
    }

    /**
     * 删除离职人员信息
     *
     * @param  Request  $request
     *
     * @return mixed
     */
    public function departureDestroy(Request $request)
    {
        $params = $request->all();
        $result = false;
        if ($request->has('id')) {
            $personnel = Personnel::query()->where('id', $params['personnel_id'])->update(['status' => 0]);
            $delCount = PersonnelDeparture::destroy($params['id']);
            $result = $delCount !== 0;
        }

        $message = $result ? '删除成功' : '删除失败';

        return response()->formatJson($result, $message);
    }

    /**
     * 工伤人员列表
     *
     * @param  Request  $request
     *
     * @return mixed
     */
    public function injuredList(Request $request)
    {
        $params = $countParams = $request->all();
        unset($countParams['pageNumber'], $countParams['pageSize']);
        $data = $this->getInjuredList($params)->get()->toArray();
        if ($data) {
            $data = $this->injuredTransData($data);
        }
        $dataCount = $this->getInjuredList($countParams)->count();
        return response()->formatJson(['personnel' => $data, 'count' => $dataCount]);
    }

    public function getInjuredList($params): \Illuminate\Database\Eloquent\Builder
    {
        $query = PersonnelInjured::query();

        if (isset($params['personnel_id'])) {
            $query->where('personnel_id', $params['personnel_id']);
        }

        $query->with('personnel');

        // if (isset($params['pageNumber'], $params['pageSize'])) {
        //     $query
        //         ->limit($params['pageSize'])
        //         ->offset(($params['pageNumber'] - 1) * $params['pageSize']);
        // }

        return $query;
    }

    /**
     * 数据转化
     *
     * @param  array  $data
     *
     * @return array
     */
    public function injuredTransData(array $data): array
    {
        foreach ($data as $key => $personnel) {
            foreach ($personnel as $filed => $value) {

                if ($filed === 'personnel') {
                    $data[$key]['name'] = $value['name'];
                    $data[$key]['join_at'] = $value['join_at'];
                    $data[$key]['IDCard'] = $value['IDCard'];
                    $data[$key]['phone'] = $value['phone'];
                    $data[$key]['age'] = IDCardController::getAge($value['IDCard']);
                    $data[$key]['sex'] = IDCardController::getSex($value['IDCard']);
                }

            }
        }

        return $data;
    }

    /**
     * 添加离职人员
     */
    public function addInjuredPersonnel(Request $request)
    {
        $params = $request->all();
        unset($params['name'], $params['IDCard'], $params['phone'], $params['join_at'], $params['age'], $params['sex']);
        $personnel = PersonnelDriver::query()->where('personnel_id',
            $params['personnel_id'])->update(['is_injured' => 1]);
        $params['created_at'] = date('Y-m-d H:i:s');
        $result = PersonnelInjured::insert($params);
        $message = $result ? '添加成功' : '添加失败';
        return response()->formatJson($result, $message);
    }

    /**
     * 添加工伤人员
     */
    public function editInjuredPersonnel(Request $request)
    {
        $params = $request->all();
        unset($params['personnel_name'], $params['IDCard'], $params['IDCard'], $params['phone'], $params['join_at'], $params['age'], $params['sex']);
        $personnel = PersonnelDriver::query()->where('personnel_id',
            $params['personnel_id'])->update(['is_injured' => 1]);
        $result = PersonnelInjured::where('id', $params['id'])
            ->update([
                'injured_reason' => $params['injured_reason'], 'injured_at' => $params['injured_at'],
                'updated_at'     => date('Y-m-d H:i:s'),
            ]);
        $message = $result ? '添加成功' : '添加失败';
        return response()->formatJson($result, $message);
    }

    /**
     * 删除离职人员信息
     *
     * @param  Request  $request
     *
     * @return mixed
     */
    public function injuredDestroy(Request $request)
    {
        $params = $request->all();
        $result = false;
        if ($request->has('id')) {
            $personnel = Personnel::query()->where('id', $params['personnel_id'])->update(['status' => 0]);
            $delCount = PersonnelInjured::destroy($params['id']);
            $result = $delCount !== 0;
        }

        $message = $result ? '删除成功' : '删除失败';

        return response()->formatJson($result, $message);
    }

    /**
     * 下载图片
     *
     * @param  Request  $request
     *
     * @return \Symfony\Component\HttpFoundation\BinaryFileResponse
     */
    public function downLoadOne(Request $request)
    {
        $params = $request->input();
        $url_arr = parse_url($params['url']);
        return response()->download(substr($url_arr['path'], 1));
    }

    /**
     * 下载个人所有附件
     *
     * @param  Request  $request
     *
     * @return \Symfony\Component\HttpFoundation\BinaryFileResponse
     */
    public function downLoad(Request $request)
    {
        $params = $request->input();
        $personnelFile = PersonnelFile::where('personnel_id', $params['id'])->get()->toArray();
        $url_arr = parse_url($personnelFile[0]['file_path']);
        $path_arr = explode('/', substr($url_arr['path'], 1));
        unset($path_arr[count($path_arr) - 1]);
        $folder = implode('/', $path_arr);
        // file
        $zip_file = $path_arr[count($path_arr) - 1].'.zip';    //下载的zip的文件名

        $zip = new \ZipArchive();
        $c = $zip->open($zip_file, \ZipArchive::CREATE | \ZipArchive::OVERWRITE);
        $files = new \RecursiveIteratorIterator(new \RecursiveDirectoryIterator($folder));
        foreach ($files as $name => $file) {
            // 我们要跳过所有子目录
            if (! $file->isDir()) {
                $filePath = $file->getRealPath();

                // 用 substr/strlen 获取文件扩展名
                $file_arr = explode('/', $filePath);
                $relativePath = $file_arr[count($file_arr) - 1];
                file_put_contents('1.txt', $filePath);
                $zip->addFile($filePath, $relativePath);
            }
        }
        $zip->close();
        return response()->download($zip_file);
    }

    /**
     * 导出人员信息
     *
     * @param  Request  $request
     *
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     * @throws \PhpOffice\PhpSpreadsheet\Writer\Exception
     */
    public function exportPersonnel(Request $request)
    {
        $params = $request->input();
        $Letter = [
            'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V',
        ];

        $data = $this->getPersonnelList($params)->get()->toArray();

        $data = $this->transData($data);
        // 创建一个Spreadsheet对象
        $spreadsheet = new Spreadsheet();
        // 设置文档属性
        $spreadsheet->getProperties()->setCreator('Maarten Balliauw')
            ->setLastModifiedBy('Maarten Balliauw')
            ->setTitle('Office 2007 XLSX Test Document')
            ->setSubject('Office 2007 XLSX Test Document')
            ->setDescription('Test document for Office 2007 XLSX, generated using PHP classes.')
            ->setKeywords('office 2007 openxml php')
            ->setCategory('Test result file');

        // 添加表头
        $spreadsheet->setActiveSheetIndex(0)
            ->setCellValue('A1', '安康市公交行政楼工作人员花名册')
            ->setCellValue('A2', '部门')->setCellValue('B2', '序号')->setCellValue('C2', '姓  名')->setCellValue('D2', '职  务')
            ->setCellValue('E2', '入职时间')->setCellValue('F2', '转正时间')->setCellValue('G2', '合同到期时间')
            ->setCellValue('H2', '身份证号码')->setCellValue('I2', '年龄')->setCellValue('J2', '性别')
            ->setCellValue('K2', '身份证住址')->setCellValue('L2', '联系方式')
            ->setCellValue('M2', '养老入保时间/弃保')->setCellValue('N2', '失业入保时间/弃保')->setCellValue('O2', '医疗入保时间/弃保')
            ->setCellValue('P2', '工伤入保时间/弃保')->setCellValue('Q2', '生育入保时间/弃保')
            ->setCellValue('R2', '学历')->setCellValue('S2', '毕业院校')->setCellValue('T2', '资格证')->setCellValue('U2',
                '离职时间')
            ->setCellValue('V2', '备注');
        //居中
        $numberStyleCenter = [
            'alignment' => [
                'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
                'vertical'   => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER,
            ],
        ];
        $spreadsheet->getActiveSheet()->getStyle('A1')->applyFromArray($numberStyleCenter)->getAlignment()->setWrapText(true);
        $num = 3;
        foreach ($data as $dk => $dv) {
            $spreadsheet->getActiveSheet()->setCellValue('A'.$num, $dv['department']);
            $spreadsheet->getActiveSheet()->setCellValue('B'.$num, $dk + 1);
            $spreadsheet->getActiveSheet()->setCellValue('C'.$num, $dv['name']);
            $spreadsheet->getActiveSheet()->setCellValue('D'.$num, $dv['office']);
            $spreadsheet->getActiveSheet()->setCellValue('E'.$num, $dv['join_at']);
            $spreadsheet->getActiveSheet()->setCellValue('F'.$num, $dv['positive_at']);
            $spreadsheet->getActiveSheet()->setCellValue('G'.$num, $dv['contract_expire_at']);
            $spreadsheet->getActiveSheet()->setCellValue('H'.$num, '`'.$dv['IDCard']);
            $spreadsheet->getActiveSheet()->setCellValue('I'.$num, $dv['age']);
            $spreadsheet->getActiveSheet()->setCellValue('J'.$num, $dv['sex']);
            $spreadsheet->getActiveSheet()->setCellValue('K'.$num, $dv['IDCard_address']);
            $spreadsheet->getActiveSheet()->setCellValue('L'.$num, $dv['phone']);
            $spreadsheet->getActiveSheet()->setCellValue('M'.$num, $dv['endowment_insurance_payment_at']);
            $spreadsheet->getActiveSheet()->setCellValue('N'.$num, $dv['unemployment_insurance_payment_at']);
            $spreadsheet->getActiveSheet()->setCellValue('O'.$num, $dv['medical_insurance_payment_at']);
            $spreadsheet->getActiveSheet()->setCellValue('P'.$num, $dv['injured_insurance_payment_at']);
            $spreadsheet->getActiveSheet()->setCellValue('Q'.$num, $dv['maternity_insurance_payment_at']);
            $spreadsheet->getActiveSheet()->setCellValue('R'.$num, $dv['education']);
            $spreadsheet->getActiveSheet()->setCellValue('S'.$num, $dv['graduated_school']);
            $spreadsheet->getActiveSheet()->setCellValue('T'.$num, '');
            $spreadsheet->getActiveSheet()->setCellValue('U'.$num, $dv['departure_at']);
            $spreadsheet->getActiveSheet()->setCellValue('V'.$num, $dv['note']);
            $num++;
        }

        // 合并行、列
        $spreadsheet->getActiveSheet()->mergeCells('A1:N1');

        //  设置宽度
        $spreadsheet->getActiveSheet()->getColumnDimension('A')->setWidth(16.25);
        $spreadsheet->getActiveSheet()->getColumnDimension('B')->setWidth(3.5);
        $spreadsheet->getActiveSheet()->getColumnDimension('C')->setWidth(16.38);
        $spreadsheet->getActiveSheet()->getColumnDimension('D')->setWidth(16.88);
        $spreadsheet->getActiveSheet()->getColumnDimension('E')->setWidth(16.88);
        $spreadsheet->getActiveSheet()->getColumnDimension('F')->setWidth(15.25);
        $spreadsheet->getActiveSheet()->getColumnDimension('G')->setWidth(10.38);
        $spreadsheet->getActiveSheet()->getColumnDimension('H')->setWidth(26.38);
        $spreadsheet->getActiveSheet()->getColumnDimension('I')->setWidth(6.38);
        $spreadsheet->getActiveSheet()->getColumnDimension('J')->setWidth(7.5);
        $spreadsheet->getActiveSheet()->getColumnDimension('K')->setWidth(35);
        $spreadsheet->getActiveSheet()->getColumnDimension('L')->setWidth(21);
        $spreadsheet->getActiveSheet()->getColumnDimension('M')->setWidth(17);
        $spreadsheet->getActiveSheet()->getColumnDimension('N')->setWidth(15.38);
        $spreadsheet->getActiveSheet()->getColumnDimension('O')->setWidth(15.38);
        $spreadsheet->getActiveSheet()->getColumnDimension('P')->setWidth(15.38);
        $spreadsheet->getActiveSheet()->getColumnDimension('Q')->setWidth(15.38);
        $spreadsheet->getActiveSheet()->getColumnDimension('R')->setWidth(8.38);
        $spreadsheet->getActiveSheet()->getColumnDimension('S')->setWidth(25.38);
        $spreadsheet->getActiveSheet()->getColumnDimension('T')->setWidth(15.38);
        $spreadsheet->getActiveSheet()->getColumnDimension('U')->setWidth(15.38);
        $spreadsheet->getActiveSheet()->getColumnDimension('V')->setWidth(45.38);
        //设置字体
        $spreadsheet->getActiveSheet()->getStyle('A1')->getFont()->setSize(18);
        $spreadsheet->getActiveSheet()->getStyle('A1')->applyFromArray($numberStyleCenter)->getAlignment()->setWrapText(true);

        for ($i = 0; $i < 22; $i++) {
            $spreadsheet->getActiveSheet()->getStyle($Letter[$i].'2')->getFont()->setSize(11);
            $spreadsheet->getActiveSheet()->getStyle($Letter[$i].'2')->applyFromArray($numberStyleCenter)->getAlignment()->setWrapText(true);
        }

        $spreadsheet->setActiveSheetIndex(0);

        // 重命名 worksheet
        $spreadsheet->getActiveSheet()->setTitle('行政花名册');

        // 将活动工作表索引设置为第一个工作表，以便Excel将其作为第一个工作表打开
        // 将输出重定向到客户端的Web浏览器 (Xlsx)
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename="行政花名册.xlsx"');
        header('Cache-Control: max-age=0');
        // 如果正在使用IE 9
        header('Cache-Control: max-age=1');
        // If you're serving to IE over SSL, then the following may be needed
        header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
        header('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always modified
        header('Cache-Control: cache, must-revalidate'); // HTTP/1.1
        header('Pragma: public'); // HTTP/1.0
        $writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
        $writer->save('php://output');
        exit;
    }

    /**
     * 导出驾驶人员信息
     *
     * @param  Request  $request
     *
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     * @throws \PhpOffice\PhpSpreadsheet\Writer\Exception
     */
    public function exportDriverPersonnel(Request $request)
    {
        $params = $request->input();
        $Letter = [
            'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U',
            'V', 'W', 'X', 'Y',
        ];
        $params['type'] = isset($params['type']) ? (int) $params['type'] : 1;
        $datas = $this->getPersonnelList($params)->get()->toArray();

        $datas = $this->transData($datas);
        $data = collect($datas)->groupBy('team_num')->toArray();
        // 创建一个Spreadsheet对象
        $spreadsheet = new Spreadsheet();
        // 设置文档属性
        $spreadsheet->getProperties()->setCreator('Maarten Balliauw')
            ->setLastModifiedBy('Maarten Balliauw')
            ->setTitle('Office 2007 XLSX Test Document')
            ->setSubject('Office 2007 XLSX Test Document')
            ->setDescription('Test document for Office 2007 XLSX, generated using PHP classes.')
            ->setKeywords('office 2007 openxml php')
            ->setCategory('Test result file');

        // 添加表头
        $spreadsheet->setActiveSheetIndex(0)
            ->setCellValue('A1', '总序')->setCellValue('B1', '队序')->setCellValue('C1', '线路')->setCellValue('D1', '姓名')
            ->setCellValue('E1', '入职时间')->setCellValue('F1', '合同到期')->setCellValue('G1', '安全责任书')
            ->setCellValue('H1', '工龄')->setCellValue('I1', '联系电话')->setCellValue('J1', '身份证号码')
            ->setCellValue('K1', '年龄')->setCellValue('L1', '性别')->setCellValue('M1', '住址')
            ->setCellValue('N1', '养老入保时间/弃保')->setCellValue('O1', '失业入保时间/弃保')->setCellValue('P1', '医疗入保时间/弃保')
            ->setCellValue('Q1', '工伤入保时间/弃保')->setCellValue('R1', '生育入保时间/弃保')
            ->setCellValue('S1', '驾驶证有效期')->setCellValue('T1', '资格证有效期')->setCellValue('U1', '继续教育记录')
            ->setCellValue('V1', '诚信考核记录')->setCellValue('W1', '准驾证')->setCellValue('X1', '是否加入工会')->setCellValue('Y1',
                '备注');
        //居中
        $numberStyleCenter = [
            'alignment' => [
                'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
                'vertical'   => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER,
            ],
        ];
        $spreadsheet->getActiveSheet()->getStyle('A1')->applyFromArray($numberStyleCenter)->getAlignment()->setWrapText(true);
        $num = 2;
        foreach ($data as $dk => $dv) {
            foreach ($dv as $k => $v) {
                $spreadsheet->getActiveSheet()->setCellValue('A'.$num, $num - 1);
                $spreadsheet->getActiveSheet()->setCellValue('B'.$num, $k + 1);
                $spreadsheet->getActiveSheet()->setCellValue('C'.$num, $v['line_num']);
                $spreadsheet->getActiveSheet()->setCellValue('D'.$num, $v['name']);
                $spreadsheet->getActiveSheet()->setCellValue('E'.$num, $v['join_at']);
                $spreadsheet->getActiveSheet()->setCellValue('F'.$num, $v['contract_expire_at']);
                $spreadsheet->getActiveSheet()->setCellValue('G'.$num, '');
                $spreadsheet->getActiveSheet()->setCellValue('H'.$num, date('Y') - date('Y', strtotime($v['join_at'])));
                $spreadsheet->getActiveSheet()->setCellValue('I'.$num, $v['phone']);
                $spreadsheet->getActiveSheet()->setCellValue('J'.$num, PHP_EOL.$v['IDCard']);
                $spreadsheet->getActiveSheet()->setCellValue('K'.$num, $v['age']);
                $spreadsheet->getActiveSheet()->setCellValue('L'.$num, $v['sex']);
                $spreadsheet->getActiveSheet()->setCellValue('M'.$num, $v['residential_address']);
                $spreadsheet->getActiveSheet()->setCellValue('N'.$num, $v['endowment_insurance_payment_at']);
                $spreadsheet->getActiveSheet()->setCellValue('O'.$num, $v['unemployment_insurance_payment_at']);
                $spreadsheet->getActiveSheet()->setCellValue('P'.$num, $v['medical_insurance_payment_at']);
                $spreadsheet->getActiveSheet()->setCellValue('Q'.$num, $v['injured_insurance_payment_at']);
                $spreadsheet->getActiveSheet()->setCellValue('R'.$num, $v['maternity_insurance_payment_at']);
                $spreadsheet->getActiveSheet()->setCellValue('S'.$num, $v['driving_license_expire_at']);
                $spreadsheet->getActiveSheet()->setCellValue('T'.$num, $v['work_license_expire_at']);
                $spreadsheet->getActiveSheet()->setCellValue('U'.$num, '');
                $spreadsheet->getActiveSheet()->setCellValue('V'.$num, '');
                $spreadsheet->getActiveSheet()->setCellValue('W'.$num, '');
                $spreadsheet->getActiveSheet()->setCellValue('X'.$num, isset($v['is_join_tu']) == 1 ? '是' : '否');
                $spreadsheet->getActiveSheet()->setCellValue('Y'.$num, $v['note']);
                $num++;
            }
        }

        // 合并行、列

        //  设置宽度
        $spreadsheet->getActiveSheet()->getColumnDimension('A')->setWidth(4.25);
        $spreadsheet->getActiveSheet()->getColumnDimension('B')->setWidth(4.5);
        $spreadsheet->getActiveSheet()->getColumnDimension('C')->setWidth(4.38);
        $spreadsheet->getActiveSheet()->getColumnDimension('D')->setWidth(10.88);
        $spreadsheet->getActiveSheet()->getColumnDimension('E')->setWidth(13.88);
        $spreadsheet->getActiveSheet()->getColumnDimension('F')->setWidth(13.25);
        $spreadsheet->getActiveSheet()->getColumnDimension('G')->setWidth(10.38);
        $spreadsheet->getActiveSheet()->getColumnDimension('H')->setWidth(3.38);
        $spreadsheet->getActiveSheet()->getColumnDimension('I')->setWidth(16.38);
        $spreadsheet->getActiveSheet()->getColumnDimension('J')->setWidth(17.5);
        $spreadsheet->getActiveSheet()->getColumnDimension('K')->setWidth(4);
        $spreadsheet->getActiveSheet()->getColumnDimension('L')->setWidth(4);
        $spreadsheet->getActiveSheet()->getColumnDimension('M')->setWidth(37);
        $spreadsheet->getActiveSheet()->getColumnDimension('N')->setWidth(13.38);
        $spreadsheet->getActiveSheet()->getColumnDimension('O')->setWidth(13.38);
        $spreadsheet->getActiveSheet()->getColumnDimension('P')->setWidth(13.38);
        $spreadsheet->getActiveSheet()->getColumnDimension('Q')->setWidth(13.38);
        $spreadsheet->getActiveSheet()->getColumnDimension('R')->setWidth(13.38);
        $spreadsheet->getActiveSheet()->getColumnDimension('S')->setWidth(25.38);
        $spreadsheet->getActiveSheet()->getColumnDimension('T')->setWidth(15.38);
        $spreadsheet->getActiveSheet()->getColumnDimension('U')->setWidth(15.38);
        $spreadsheet->getActiveSheet()->getColumnDimension('V')->setWidth(15.38);
        $spreadsheet->getActiveSheet()->getColumnDimension('W')->setWidth(15.38);
        $spreadsheet->getActiveSheet()->getColumnDimension('X')->setWidth(15.38);
        $spreadsheet->getActiveSheet()->getColumnDimension('Y')->setWidth(45.38);
        //设置字体

        for ($i = 0; $i < 22; $i++) {
            $spreadsheet->getActiveSheet()->getStyle($Letter[$i].'1')->getFont()->setSize(11);
            $spreadsheet->getActiveSheet()->getStyle($Letter[$i].'1')->applyFromArray($numberStyleCenter)->getAlignment()->setWrapText(true);
        }

        $spreadsheet->setActiveSheetIndex(0);

        // 重命名 worksheet
        $spreadsheet->getActiveSheet()->setTitle('驾驶员档案');

        // 将活动工作表索引设置为第一个工作表，以便Excel将其作为第一个工作表打开
        // 将输出重定向到客户端的Web浏览器 (Xlsx)
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename="驾驶员档案.xlsx"');
        header('Cache-Control: max-age=0');
        // 如果正在使用IE 9
        header('Cache-Control: max-age=1');
        // If you're serving to IE over SSL, then the following may be needed
        header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
        header('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always modified
        header('Cache-Control: cache, must-revalidate'); // HTTP/1.1
        header('Pragma: public'); // HTTP/1.0
        $writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
        $writer->save('php://output');
        exit;
    }
}
